﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class DiaryCommentDao
    {
        public bool Delete(SqlConnection conn, int commentId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Diary_Comment] ");
            builder.Append("WHERE ");
            builder.Append("\t[CommentId] = @CommentId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@CommentId", SqlDbType.Int);
            commandParameters[0].Value = commentId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Insert(SqlConnection conn, DiaryCommentData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT [dbo].[Diary_Comment] ");
            builder.Append("( ");
            builder.Append("    [DiaryId], ");
            builder.Append("    [UserId], ");
            builder.Append("    [Content], ");
            builder.Append("    [SendTime] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("    @DiaryId, ");
            builder.Append("    @UserId, ");
            builder.Append("    @Content, ");
            builder.Append("    @SendTime ");
            builder.Append(") ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@DiaryId", SqlDbType.Int);
            commandParameters[0].Value = data.DiaryId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.UserId;
            commandParameters[2] = new SqlParameter("@Content", SqlDbType.NText);
            commandParameters[2].Value = data.Content;
            commandParameters[3] = new SqlParameter("@SendTime", SqlDbType.DateTime);
            commandParameters[3].Value = data.SendTime;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool IsExists(SqlConnection conn, int diaryId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF EXISTS ");
            builder.Append("( ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[CommentId] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Diary_Comment] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[DiaryId] = @DiaryId ");
            builder.Append(") ");
            builder.Append("\tSELECT 1 ");
            builder.Append("ELSE ");
            builder.Append("\tSELECT 0 ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@DiaryId", SqlDbType.Int) };
            commandParameters[0].Value = diaryId;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            return ((obj2 != null) && Convert.ToBoolean(obj2));
        }

        public DataTable SelectAll(SqlConnection conn, int diaryId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Diary_Comment].*, ");
            builder.Append("\t[Users].[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Diary_Comment], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t[Diary_Comment].[DiaryId] = @DiaryId ");
            builder.Append("\tAND [Diary_Comment].[UserId] = [Users].[UserId] ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@DiaryId", SqlDbType.Int) };
            commandParameters[0].Value = diaryId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Diary_Comment";
            return set.Tables[0];
        }
    }
}

